package br.skytef.stmanager.daos;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import br.skytef.stmanager.entities.ErroPlanilhaEntity;
import br.skytef.stmanager.utils.Pool;

public class ErroPlanilhaDAO{
	
	private Connection conexao;

	public boolean inserir(ErroPlanilhaEntity erro){
		conexao = Pool.getConnection();
		PreparedStatement preparedStmt;
		try {
			preparedStmt = conexao.prepareStatement("INSERT INTO erroPlanilha(controlPlanilha_idcontrolPlanilha, erro) VALUES (?,?);");
			preparedStmt.setInt(1, erro.getControlPlanilha_idcontrolPlanilha());
			preparedStmt.setString(2, erro.getErro());
			preparedStmt.execute();
			conexao.close();			
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}		
		return true;		
	}
	public boolean delete(int idPlan){
		conexao = Pool.getConnection();
		PreparedStatement preparedStmt;
		try {
			preparedStmt = conexao.prepareStatement("DELETE FROM erroPlanilha WHERE controlPlanilha_idcontrolPlanilha ="+idPlan+";");
			preparedStmt.execute();
			conexao.close();			
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}		
		return true;		
	}
	public List<ErroPlanilhaEntity> listar(){
		conexao = Pool.getConnection();
		try {
			String query = "SELECT * FROM erroPlanilha ORDER BY controlPlanilha_idControlPlanilha;";
			Statement st = conexao.createStatement();
			ResultSet rs = st.executeQuery(query);
			List <ErroPlanilhaEntity> erros = null;
			if (rs.next()) {   
				erros = new ArrayList<ErroPlanilhaEntity>();
				do {                      
					ErroPlanilhaEntity erro = new ErroPlanilhaEntity();
					erro.setErro(rs.getString("erro"));
					erros.add(erro);
				} while (rs.next());
			}
			conexao.close();
			return erros;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
	}
	public List<ErroPlanilhaEntity> listarPorPlan(int idErro){
		conexao = Pool.getConnection();
		try {
			String query = "SELECT * FROM erroPlanilha WHERE controlPlanilha_idcontrolPlanilha ="+idErro+";";
			Statement st = conexao.createStatement();
			ResultSet rs = st.executeQuery(query);
			List <ErroPlanilhaEntity> erros = null;
			if (rs.next()) {   
				erros = new ArrayList<ErroPlanilhaEntity>();
				do {                      
					ErroPlanilhaEntity erro = new ErroPlanilhaEntity();
					erro.setErro(rs.getString("erro"));
					erros.add(erro);
				} while (rs.next());
			}
			conexao.close();
			return erros;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}		
	}
}
